﻿create 
proc sp_MaNVL_Tang
as
declare @ma_next nvarchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaNVL) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.NguyenVatLieu
	where MaNVL like 'NVL%'
	-- tao makh ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'NVL'+ right( '00' + cast(@max as nvarchar(10)) , 5)
	if(@max >=10 and @max <100)
		set @ma_next = 'NVL'+ right( '0' + cast(@max as nvarchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'NVL'+ right( '' + cast(@max as nvarchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaNVL from dbo.NguyenVatLieu where MaNVL = @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'NVL'+ right( '0' + cast(@max as nvarchar(5)) , 5)
	end
	select @ma_next as Ma
go
create 
proc sp_MaLoaiNVL_Tang
as
declare @ma_next nvarchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaLoaiNVL) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.LoaiNVL
	where MaLoaiNVL like 'LVL%'
	-- tao makh ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'LVL'+ right( '00' + cast(@max as nvarchar(10)) , 5)
	if(@max >=10 and @max <100)
		set @ma_next = 'LVL'+ right( '0' + cast(@max as nvarchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'LVL'+ right( '' + cast(@max as nvarchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaLoaiNVL from dbo.LoaiNVL where MaLoaiNVL = @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'LVL'+ right( '0' + cast(@max as nvarchar(5)) , 5)
	end
	select @ma_next as Ma
go
create 
proc sp_MaPN_Tang
as
declare @ma_next nvarchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaPN) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.PhieuNhap
	where MaPN like 'PN%'
	-- tao makh ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'PN'+ right( '00' + cast(@max as nvarchar(10)) , 5)
	if(@max >=10 and @max <100)
		set @ma_next = 'PN'+ right( '0' + cast(@max as nvarchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'PN'+ right( '' + cast(@max as nvarchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaPN from dbo.PhieuNhap where MaPN = @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'PN'+ right( '0' + cast(@max as nvarchar(5)) , 5)
	end
	select @ma_next as Ma
go
--ma phieu xuat tang
create 
proc sp_MaPX_Tang
as
declare @ma_next nvarchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaPX) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.PhieuXuat
	where MaPX like 'PX%'
	-- tao makh ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'PX'+ right( '00' + cast(@max as nvarchar(10)) , 5)
	if(@max >=10 and @max <100)
		set @ma_next = 'PX'+ right( '0' + cast(@max as nvarchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'PX'+ right( '' + cast(@max as nvarchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaPX from dbo.PhieuXuat where MaPX = @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'PX'+ right( '0' + cast(@max as nvarchar(5)) , 5)
	end
	select @ma_next as Ma
go
--ma nha cung cap tang
create  
proc sp_MaNCC_Tang 
as
declare @ma_next nvarchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaNCC) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.NCC
	where MaNCC like 'NCC%'
	-- tao makh ke tiep
	--if(@max >0 and @max <10)
	--	set @ma_next = 'NCC'+ right( '00' + cast(@max as nvarchar(10)) , 5)
	if(@max >=0 and @max <10)
		set @ma_next = 'NCC'+ right( '0' + cast(@max as nvarchar(10)) , 5)
	if(@max >=10 and @max <100)
		set @ma_next = 'NCC'+ right( '' + cast(@max as nvarchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaNCC from dbo.NCC where MaNCC = @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'NCC'+ right( '0' + cast(@max as nvarchar(5)) , 5)
	end
	select @ma_next as Ma
---------------------------------------------------------------------------------------------------------------------
go
--------------------------------------LOAI NGUYEN VAT LIEU------------------------------------------------
create 
proc sp_ThemLoaiNVL @MaLoaiNVL nvarchar(10),@TenLoaiNVL nvarchar(50)
as
	insert into LoaiNVL (MaLoaiNVL,TenLoaiNVL,isdelete)
	values(@MaLoaiNVL,@TenLoaiNVL,'false')
go
create 
proc sp_DanhSachLoaiNVL
as
	select * from LoaiNVL where isdelete = 'false'
go
create 
proc sp_DanhSachLoaiNVL_Ma @MaLoaiNVL nvarchar(10)
as
	select * from LoaiNVL where MaLoaiNVL = @MaLoaiNVL and isdelete = 'false'
go
create 
proc sp_CapNhatLoaiNVL @MaLoaiNVL nvarchar(10),@TenLoaiNVL nvarchar(50)
as
	update LoaiNVL
	set TenLoaiNVL = @TenLoaiNVL
	where MaLoaiNVL = @MaLoaiNVL
go
create 
proc sp_XoaLoaiNVL @MaLoaiNVL nvarchar(10)
as
	update LoaiNVL
	set isdelete = 'true'
	where MaLoaiNVL = @MaLoaiNVL
----------------------------------------------NGUYEN VAT LIEU----------------------------------------------------------
go
create 
proc sp_ThemNguyenVatLieu @MaNVL nvarchar(10),@MaLoaiNVL nvarchar(10),
			@TenNVL nvarchar(50),@SoLuong int, @SoLuongTon int, @DVT nvarchar(50)
as
	insert into NguyenVatLieu(MaNVL,MaLoaiNVL,TenNVL,SoLuongTon,DVT,isdelete)
	values(@MaNVL,@MaLoaiNVL,@TenNVL,@SoLuongTon,@DVT,'false')
go
--exec sp_ThemNguyenVatLieu'NVL002','LVL001','chưa  biết',100,40,
create 
proc sp_DanhSachNVL
as
	select * from NguyenVatLieu where isdelete = 'false'
go
create 
proc sp_DanhSachNVL_Ma @MaNVL nvarchar(10)
as
	select * from NguyenVatLieu where MaNVL = @MaNVL and isdelete = 'false'
go

create 
proc sp_CapNhatNVL @MaNVL nvarchar(10),@MaLoaiNVL nvarchar(10),
			@TenNVL nvarchar(50),@SoLuongTon int, @DVT nvarchar(50)
as
	update NguyenVatLieu
	set MaLoaiNVL = @MaLoaiNVL,TenNVL = @TenNVL,SoLuongTon = @SoLuongTon,
		DVT = @DVT
	where MaNVL = @MaNVL
go

create 
proc sp_XoaNVL @MaNVL nvarchar(10)
as
	update NguyenVatLieu
	set isdelete = 'true'
	where MaNVL = @MaNVL
go
create 
proc sp_DanhSachNVL_MaLoaiNVL @MaLoaiNVL nvarchar(10)
as
	select * from NguyenVatLieu where MaLoaiNVL = @MaLoaiNVL and isdelete = 'false'
---------------------------------------QUAN LY NHA CUNG CAP---------------------------------------------
go
create 
proc sp_ThemNCC @MaNCC nvarchar(5),@TenNCC nvarchar(50),@DiaChi nvarchar(50), @DienThoai nvarchar(50)
as
	insert into NCC(MaNCC,TenNCC,DiaChi,DienThoai,isdelete)
	values(@MaNCC,@TenNCC,@DiaChi,@DienThoai,'false')
go
create 
proc sp_DanhSachNCC
as
	select * from NCC where isdelete = 'false'
go
create 
proc sp_DanhSachNCC_Ma @MaNCC nvarchar(5)
as
	select * from NCC where MaNCC = @MaNCC and isdelete = 'false'
go

create 
proc sp_CapNhatNCC @MaNCC nvarchar(5),@TenNCC nvarchar(50),@DiaChi nvarchar(50), @DienThoai nvarchar(50)
as
	update NCC
	set TenNCC = @TenNCC,DiaChi = @DiaChi,DienThoai = @DienThoai
	where MaNCC = @MaNCC
go

create 
proc sp_XoaNCC @MaNCC nvarchar(5)
as
	update NCC
	set isdelete = 'true'
	where MaNCC = @MaNCC
-----------------------------QUAN LY CHI TIET NHA CUNG CAP--------------------------------
go
create 
proc sp_CTNCC @MaNCC nvarchar(5),@MaNVL nvarchar(10)
as
	insert into CTNCC(MaNCC,MaNVL)
	values(@MaNCC,@MaNVL)
go
--danh sach nha cung cap theo nguyen vat lieu 
create 
proc sp_DanhSachNCC_MaNVL  @MaNVL nvarchar(10)
as
	select * from NCC ncc, CTNCC ct, NguyenVatLieu n
	where n.MaNVL = ct.MaNVL and ct.MaNCC = ncc.MaNCC and n.MaNVL = @MaNVL and ncc.isdelete = 'false'
go
-------------------------------QUAN LY PHIEU NHAP VA CHI TIET PHIEU NHAP--------------------------
create
proc sp_ThemChiTietPhieuNhap @MaPN nvarchar(5),@MaNVL nvarchar(10), @MaNCC nvarchar(5),@DonGia decimal(18,0)
		,@SoLuong int, @SoLuongTonHT int
as
insert into CTPhieuNhap(MaPN,MaNVL,MaNCC,DonGiaNhap,SoLuong,SoLuongTonHT)
values(@MaPN,@MaNVL,@MaNCC,@DonGia,@SoLuong,@SoLuongTonHT)
GO
create 
proc sp_ThemPhieuNhap @MaPN nvarchar(5),@NgayLap date, @MaNV nvarchar(5)
as
insert into PhieuNhap(MaPN,NgayLap,MaNV)
values(@MaPN,@NgayLap,@MaNV)
go
------------------------------QUAN LY PHIEU XUAT VA CHI TIET PHIEU XUAT---------------------------
create
proc sp_ThemChiTietPhieuXuat @MaPX nvarchar(5),@MaNVL nvarchar(10),@SoLuong int, @SoLuongTonHT int
as
insert into CTPhieuXuat(MaPX,MaNVL,SoLuong,SoLuongTonHT)
values(@MaPX,@MaNVL,@SoLuong,@SoLuongTonHT)
GO
create 
proc sp_ThemPhieuXuat @MaPX nvarchar(5),@NgayLap date, @MaNV nvarchar(5)
as
insert into PhieuXuat(MaPX,NgayLap,MaNV)
values(@MaPX,@NgayLap,@MaNV)
go
-----------------------------THONG KE -----------------------------------------------------------
--bao cao tinh hinh xuat nguyen vat lieu....tu ngay .... den ngay
create 
proc TK_TinhHinhXuatNVL @MaLoaiNVL nvarchar(10),@NgayBD date,@NgayKT date
as
	if(@MaLoaiNVL = 'null')
		begin
			select px.MaPX,n.MaNVL,n.TenNVL, l.MaLoaiNVL,l.TenLoaiNVL,px.NgayLap,ct.SoLuong,ct.SoLuongTonHT
			from PhieuXuat px, CTPhieuXuat ct, NguyenVatLieu n, LoaiNVL l
			where px.MaPX = ct.MaPX and ct.MaNVL = n.MaNVL and n.MaLoaiNVL = l.MaLoaiNVL
			and px.NgayLap between @NgayBD and @NgayKT
		end
	else
		begin
			select px.MaPX,n.MaNVL,n.TenNVL, l.MaLoaiNVL,l.TenLoaiNVL,px.NgayLap,ct.SoLuong,ct.SoLuongTonHT
			from PhieuXuat px, CTPhieuXuat ct, NguyenVatLieu n, LoaiNVL l
			where px.MaPX = ct.MaPX and ct.MaNVL = n.MaNVL and n.MaLoaiNVL = l.MaLoaiNVL
			and px.NgayLap between @NgayBD and @NgayKT and l.MaLoaiNVL = @MaLoaiNVL
		end
--exec TK_TinhHinhXuatNVL 'LVL002','1-1-2013','4-4-2014'
--bao cao tinh hinh nhap nguyen vat lieu....tu ngay...den ngay
go 
create 
proc TK_TinhHinhNhapNVL @MaLoaiNVL nvarchar(10),@NgayBD date,@NgayKT date
as
	if(@MaLoaiNVL = 'null')--null
	begin
		select pn.MaPN,n.MaNVL,n.TenNVL,l.MaLoaiNVL,l.TenLoaiNVL,ncc.MaNCC,ncc.TenNCC,pn.NgayLap,ctpn.SoLuong,ctpn.SoLuongTonHT
		from PhieuNhap pn, CTPhieuNhap ctpn, NCC ncc,NguyenVatLieu n, LoaiNVL l,CTNCC ctncc
		where l.MaLoaiNVL = n.MaLoaiNVL and n.MaNVL = ctpn.MaNVL and ctpn.MaPN = pn.MaPN
		and ctpn.MaNCC = ncc.MaNCC and ncc.MaNCC = ctncc.MaNCC and ctncc.MaNVL = n.MaNVL
		and pn.NgayLap between @NgayBD and @NgayKT 
	end
	else
		begin
			select pn.MaPN,n.MaNVL,n.TenNVL,l.MaLoaiNVL,l.TenLoaiNVL,ncc.MaNCC,ncc.TenNCC,pn.NgayLap,ctpn.SoLuong,ctpn.SoLuongTonHT
			from PhieuNhap pn, CTPhieuNhap ctpn, NCC ncc,NguyenVatLieu n, LoaiNVL l,CTNCC ctncc
			where l.MaLoaiNVL = n.MaLoaiNVL and n.MaNVL = ctpn.MaNVL and ctpn.MaPN = pn.MaPN
			and ctpn.MaNCC = ncc.MaNCC and ncc.MaNCC = ctncc.MaNCC and ctncc.MaNVL = n.MaNVL
			and pn.NgayLap between @NgayBD and @NgayKT and l.MaLoaiNVL = @MaLoaiNVL
		end
--exec TK_TinhHinhNhapNVL 'LVL001','1-1-2013','4-4-2014'
--thong ke danh sach nguyen vat lieu theo loai nguyen vat lieu
go
create 
proc TK_DanhSachNVL_LoaiNVL @MaLoaiNVL nvarchar(10)
as
	select * from NguyenVatLieu where MaLoaiNVL = @MaLoaiNVL
--exec TK_DanhSachNVL_LoaiNVL 'LVL002'
